﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace LambdaToSql.DbHelper
{
    internal class MsSqlServer : IDBhelper
    {
        internal LambdaToSql.EntityModel.DbContext Context { get; set; }

        #region MS Sql Server IDataReader 

        /// <summary>
        /// 返回IDataReader
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameter">object参数数组</param>

        /// <returns>返回IDataReader</returns>
        public virtual IDataReader ExecuteReader(string sqlStr, object parameter = null)
        {
            using (var Comm = GetSqlCommand(sqlStr, parameter))
            {
                IDataReader sdr;
                if (this.Context.Trans != null)
                {
                    sdr = (IDataReader)Comm.ExecuteReader();
                }
                else
                {
                    sdr = (IDataReader)Comm.ExecuteReader(CommandBehavior.CloseConnection);
                }
                return (IDataReader)sdr;
            }
        }
        /// <summary>
        /// 返回 IDataReader
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameters">object参数数组</param>

        /// <returns>返回值 IDataReader类型的参数</returns>
        public virtual IDataReader ExecuteReader(string sqlStr, object[] parameters)
        {
            using (var Comm = GetSqlCommand(sqlStr, parameters))
            {
                IDataReader sdr;
                if (this.Context.Trans != null)
                {
                    sdr = (IDataReader)Comm.ExecuteReader();
                }
                else
                {
                    sdr = (IDataReader)Comm.ExecuteReader(CommandBehavior.CloseConnection);
                }
                Comm.Parameters.Clear();
                return (IDataReader)sdr;
            }
        }

        #endregion IDataReader

        #region MS Sql Server DataTable

        /// <summary>
        /// 返回 DataTable
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameter">object参数数组</param>

        /// <returns>返回值 DataTable类型的参数</returns>
        public virtual DataTable ExecuteTable(string sqlStr, object parameter = null)
        {
            using (var Comm = GetSqlCommand(sqlStr, parameter))
            {
                using (SqlDataAdapter Da = new SqlDataAdapter(Comm))
                {
                    DataTable Dt = new DataTable();
                    Da.Fill(Dt);
                    SqlConnectionClose(Comm.Connection);
                    return Dt;
                }
            }
        }

        /// <summary>
        /// 返回 DataTable
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameters">object参数数组</param>
        /// <returns>返回值 DataTable类型的参数</returns>
        public virtual DataTable ExecuteTable(string sqlStr, object[] parameters)
        {
            using (var Comm = GetSqlCommand(sqlStr, parameters))
            {
                using (SqlDataAdapter Da = new SqlDataAdapter(Comm))
                {
                    DataTable Dt = new DataTable();
                    Da.Fill(Dt);
                    SqlConnectionClose(Comm.Connection);
                    Comm.Parameters.Clear();
                    return Dt;
                }
            }
        }

        #endregion

        #region MS Sql Server ExecuteScalar 

        /// <summary>
        /// 返回 object
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameter">object参数数组</param>
        /// <returns>返回值 int类型的参数</returns>
        public virtual string ExecuteScalar(string sqlStr, object parameter = null)
        {
            using (var Comm = GetSqlCommand(sqlStr, parameter))
            {
                var obj = Comm.ExecuteScalar();
                SqlConnectionClose(Comm.Connection);

                var str = obj == null ? "" : obj.ToString();
                return str;
            }
        }

        /// <summary>
        /// 返回 object
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameters">object参数数组</param>
        /// <returns>返回值 int类型的参数</returns>
        public virtual string ExecuteScalar(string sqlStr, object[] parameters)
        {
            using (var Comm = GetSqlCommand(sqlStr, parameters))
            {
                var obj = Comm.ExecuteScalar();
                SqlConnectionClose(Comm.Connection);

                var str = obj == null ? "" : obj.ToString();
                Comm.Parameters.Clear();
                return str;
            }
        }
        #endregion ExecuteScalar

        #region MS Sql Server ExecuteScalars

        /// <summary>
        /// 返回查询的第一个字段值,逗号隔开
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameter">参数列表</param>
        /// <returns></returns>
        public virtual IList<string> ExecuteScalars(string sqlStr, object parameter = null)
        {
            var list = new List<string>();
            var sdr = ExecuteReader(sqlStr, parameter);
            while (sdr.Read())
            {
                list.Add(sdr[0].ToString());
            }
            sdr.Close();
            return list;
        }
        /// <summary>
        /// 返回查询的第一个字段值,逗号隔开
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public virtual IList<string> ExecuteScalars(string sqlStr, object[] parameters)
        {
            var list = new List<string>();
            var sdr = ExecuteReader(sqlStr, parameters);
            while (sdr.Read())
            {
                list.Add(sdr[0].ToString());
            }
            sdr.Close();
            return list;
        }

        #endregion ExecuteScalars

        #region MS Sql Server ExecuteNonQuery

        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameter">object参数数组</param>
        /// <returns>返回值 int类型的参数</returns>
        /// <returns></returns>
        public virtual int ExecuteNonQuery(string sqlStr, object parameter = null)
        {
            using (var Comm = GetSqlCommand(sqlStr, parameter))
            {
                var obj = Comm.ExecuteNonQuery();
                SqlConnectionClose(Comm.Connection);
                return obj;
            }
        }

        /// <summary>
        /// 执行 ExecuteNonQuery
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="parameters">object参数数组</param>
        /// <returns>返回值 int类型的参数</returns>
        public virtual int ExecuteNonQuery(string sqlStr, object[] parameters)
        {
            using (var Comm = GetSqlCommand(sqlStr, parameters))
            {
                var obj = Comm.ExecuteNonQuery();
                SqlConnectionClose(Comm.Connection);
                Comm.Parameters.Clear();
                return obj;
            }
        }
        #endregion ExecuteNonQuery  


        #region 关闭数据库连接

        /// <summary>
        /// 关闭 SqlConnection
        /// <para>事务不需要手动关闭</para>
        /// </summary>
        /// <param name="conn"></param>
        private void SqlConnectionClose(SqlConnection conn)
        {
            if (this.Context.Trans == null)
            {
                conn.Close();
            }
        }
        #endregion

        #region 返回 SqlCommand

        /// <summary>
        /// 返回 SqlCommand
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        private SqlCommand GetSqlCommand(string sqlStr, object parameter = null)
        {
            SqlCommand Comm;
            if (this.Context.Trans != null)
            {
                Comm = new SqlCommand(sqlStr, this.Context.TransConnection);
                Comm.Transaction = this.Context.Trans;
            }
            else
            {
                var Conn = new SqlConnection(this.Context.ConnectionString);
                Comm = new SqlCommand(sqlStr, Conn);
                Conn.Open();
            }

            parameter = GetSqlParameter(parameter);
            if (parameter != null)
            {
                Comm.Parameters.Add(parameter);
            }
            return Comm;
        }

        /// <summary>
        /// 返回 SqlCommand
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private SqlCommand GetSqlCommand(string sqlStr, object[] parameters)
        {
            SqlCommand Comm;
            if (this.Context.Trans != null)
            {
                Comm = new SqlCommand(sqlStr, this.Context.TransConnection);
                Comm.Transaction = this.Context.Trans;
            }
            else
            {
                var Conn = new SqlConnection(this.Context.ConnectionString);
                Comm = new SqlCommand(sqlStr, Conn);
                Conn.Open();
            }

            parameters = GetSqlParameter(parameters);
            if (parameters != null)
            {
                foreach (object P in parameters)
                {
                    Comm.Parameters.Add(P);
                }
            }
            return Comm;
        }


        private SqlParameter GetSqlParameter(object parameter)
        {
            SqlParameter par = null;
            if (parameter != null)
            {
                var temp = parameter as LambdaToSql.EntityModel.DataParameter;
                if (temp != null)
                {
                    par = new SqlParameter(temp.Name, temp.Value);
                }
                else
                {
                    par = parameter as SqlParameter;
                }
            }
            return par;
        }
        private SqlParameter[] GetSqlParameter(object[] parameters)
        {
            var pars = new List<SqlParameter>();
            if (parameters != null)
            {
                foreach (var item in parameters)
                {
                    var temp = item as LambdaToSql.EntityModel.DataParameter;
                    if (temp != null)
                    {
                        pars.Add(new SqlParameter(temp.Name, temp.Value));
                    }
                    else
                    {
                        pars.Add(item as SqlParameter);
                    }
                }
            }
            if (pars.Count >= 0)
            {
                return pars.ToArray();
            }
            return null;
        }

        #endregion
    }
}
